Note: Please follow the following gif to create the Calculated Measures table and then create the measures using the formulas down below
=> Total Deceased = DISTINCTCOUNT(PoliceInvolvedFatalities[UID])
=> Cnt of Months = DISTINCTCOUNT('Date'[YearMonthShort])
=> Fatalities Per Month = DIVIDE([Total Deceased],[Cnt of months],0)+0
=> Fatalities Per Month YoY =
VAR __PREV_YEAR = CALCULATE([Fatalities Per Month], DATEADD('Date'[Date], -1, YEAR))
RETURN
DIVIDE([Fatalities Per Month] - __PREV_YEAR, __PREV_YEAR,0)
=> Indicator = If([Fatalities Per Month YoY%]>0,UNICHAR(11165),UNICHAR(11167))
=> KPI Data Color = If([Fatalities Per Month YoY%]>0,1,0)
=> MapColors = SWITCH(
TRUE(),
[Fatalities Per Month YoY%] >0
&& [Fatalities Per Month YoY%] <= 0.5, "#FFF176",
[Fatalities Per Month YoY%] >0.5
&& [Fatalities Per Month YoY%] <= 1.0, "#FFA726",
[Fatalities Per Month YoY%]> 1.0,"#BF360C",
[Fatalities Per Month YoY%] <=0 && [Fatalities Per Month YoY%]<=-0.5,"#1B5E20",
"#8BC34A"
)
Age Range =
SWITCH (
TRUE (),
'PoliceInvolvedFatalities'[Age]
>= 0
&&
'PoliceInvolvedFatalities'[Age] <= 18, "0-18",
'PoliceInvolvedFatalities'[Age]
> 18
&&
'PoliceInvolvedFatalities'[Age] <= 40, "19-40",
'PoliceInvolvedFatalities'[Age]
> 40
&&
'PoliceInvolvedFatalities'[Age] <= 50, "41-50",
"51 &
over"
)
Note: Copy and paste the above DAX to add "Age Range" column
